package com.cetcs.kmga.dataManager.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.cetcs.kmga.dataManager.dao.hbase.LogPage;
import com.google.common.collect.Lists;
import org.apache.hadoop.hbase.client.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

/**
 * Hbase数据库连接池
 *
 * @author xutao
 * @version V1.0 创建时间：2017-08-30 11:27
 * Copyright 2017 by CETC
 */
public class DruidH2Pool {

    private static DruidH2Pool druidPool;

    private static DruidDataSource h2Source;

    private final static String H2_PATH = "conf/druid-h2.properties";

    private final static Properties h2Properties = new Properties();

    private final static Logger LOGGER = LoggerFactory.getLogger(DruidH2Pool.class);

    private final ExecutorService FIXED_LENGTH_POOL = Executors.newFixedThreadPool(10);

    static {
        try {
            LOGGER.info("load h2 properties");
            h2Properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream(H2_PATH));
            h2Source = (DruidDataSource) DruidDataSourceFactory
                    .createDataSource(h2Properties);
            druidPool = new DruidH2Pool();
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("获取h2初始化连接池" + e.getMessage());
        }
    }

    private DruidH2Pool() {
    }

    public static DruidH2Pool getInstance() {
        return druidPool;
    }


    public Connection getConnection() {
        try {
            if (h2Source != null) {
                return h2Source.getConnection();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("获取连接失败" + e.getMessage(), e);
        }
        return null;
    }

    public static void close(Connection con) {
        try {
            if (con != null) {
                con.close();
                con = null;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            LOGGER.error("关闭连接" + e.getMessage());
        }
        return;
    }

    /**
     * 创建缓存表以及缓存数据
     *
     * @param tbName    临时表名
     * @param columnArr 数据字段 每个内层数组都两个值 第一个是字段名 第二值字段长度
     */
    public void createCacheTb(String tbName, List<List<String>> columnArr) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = getConnection();
            //如果存在就删除表
            preparedStatement = conn.prepareStatement("DROP TABLE IF EXISTS  " + tbName);
            preparedStatement.execute();
            //开始创建临时表
            String crTbSql = "CREATE TABLE IF NOT EXISTS " + tbName + "(";
            for (int i = 0; i < columnArr.size(); i++) {
                List<String> singleList = columnArr.get(i);
                if (i != 0) {
                    crTbSql += ",";
                }
                //排除超大字段，超大字段存储blob字段
                if ("CLOB".equalsIgnoreCase(singleList.get(2))) {
                    crTbSql += singleList.get(0) + " CLOB ";
                } else {
                    crTbSql += singleList.get(0) + " VARCHAR ( " + singleList.get(1) + " )";
                }
                if (i == 0) {
                    crTbSql += " PRIMARY KEY ";
                }
            }
            crTbSql += " )";
            preparedStatement = conn.prepareStatement(crTbSql);
            preparedStatement.execute();
        } catch (SQLException e) {
            LOGGER.error("创建临时表失败" + e.getMessage(), e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                close(conn);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    public void remvoeCacheTb(String tbName) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = getConnection();
            //如果存在就删除表
            preparedStatement = conn.prepareStatement("DROP TABLE IF EXISTS  " + tbName);
            preparedStatement.execute();
        } catch (SQLException e) {
            LOGGER.error("删除临时表失败" + e.getMessage(), e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                close(conn);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }

    /**
     * 开始用现成批量创建数据
     *
     * @param tbName    表的名称
     * @param columnArr 字段值
     * @param data      数据
     */
    public void createBathDataUseThread(String tbName, List<String> columnArr, List<List<String>> data, boolean isNotExport) {
        int maxSize = 500;
        int loopSize = data.size() % maxSize == 0 ? data.size()
                / maxSize : data.size() / maxSize + 1;
        //循环加载数据
        for (int loop = 0; loop < loopSize; loop++) {
            int end = (loop + 1) * maxSize > data.size() ? data
                    .size() : (loop + 1) * maxSize;
            List<List<String>> partData = data.subList(loop * maxSize,
                    end);
            if (isNotExport) {
                if (loop == 0) {
                    createBathDataIntoH2(tbName, columnArr, partData);
                } else {
                    FIXED_LENGTH_POOL.submit(() -> createBathDataIntoH2(tbName, columnArr, partData));
                }
            } else {
                createBathDataIntoH2(tbName, columnArr, partData);
            }
        }
    }

    public void createBathDataUseThread(String tbName, List<String> columnArr, List<List<String>> data) {
        this.createBathDataUseThread(tbName, columnArr, data, true);
    }

    /**
     * 开始批量创建数据
     *
     * @param tbName    表的名称
     * @param columnArr 字段数组
     * @param data
     */
    public void createBathDataIntoH2(String tbName, List<String> columnArr, List<List<String>> data) {
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        try {
            conn = getConnection();
            //开始导数据 数据是批量插入
            String insSql = "INSERT INTO " + tbName + "(";
            String tmp = "";
            for (int i = 0; i < columnArr.size(); i++) {
                if (i != 0) {
                    insSql += ",";
                    tmp += ",";
                }
                insSql += columnArr.get(i);
                tmp += "?";
            }
            insSql += ") VALUES(" + tmp + ")";
            conn.setAutoCommit(false);
            preparedStatement = conn.prepareStatement(insSql);
            for (int i = 0; i < data.size(); i++) {
                List<String> singleData = data.get(i);
                for (int j = 0; j < singleData.size(); j++) {
                    preparedStatement.setString(j + 1, singleData.get(j));
                }
                preparedStatement.addBatch();
            }
            preparedStatement.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            LOGGER.error("保存数据出错" + e.getMessage(), e);
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                close(conn);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
    }


}
